package com.dy.yunying.biz.dao.datacenter.impl;

import com.dy.yunying.api.datacenter.dto.RegRetentionDto;
import com.dy.yunying.api.datacenter.vo.RegRetentionVO;
import com.dy.yunying.biz.config.YunYingProperties;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Objects;

/**
 * @author hma
 * @date 2022/8/19 17:02
 */
@Slf4j
@Component(value = "regRetentionDao")
public class RegRetentionDao {
	@Resource(name = "dorisTemplate")
	private JdbcTemplate dorisTemplate;

	@Resource
	private YunYingProperties yunYingProperties;

	public List<RegRetentionVO> list(RegRetentionDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n" +
				"\t`day`,\n" +
				"\taccountNums,-- 新增账号数 \n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate,-- 次留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums3  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate3,-- 3留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums4  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate4,-- 4留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums5  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate5,-- 5留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums6  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate6,-- 6留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums7  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate7,-- 7留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums8  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate8,-- 8留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums9  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate9,-- 9留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums10  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate10,-- 10留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums11  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate11,-- 11留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums12  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate12,-- 12留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums13  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate13,-- 13留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums14  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate14,-- 14留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums30  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate30,-- 30留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums60  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate60,-- 60留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums90  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate90,-- 90留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums120  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate120,-- 120留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums150  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate150,-- 150留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums180  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate180,-- 180留\n" +
				"\tround( IF ( accountNums > 0, divide ( retentionNums360  * 100.00, accountNums ), 0 ), 2 ) AS retentionRate360 -- 360留\n" +
				"FROM\n" +
				"(")
				.append(getBaseSql(req)).append(" ) a \n");
		if (StringUtils.isNotBlank(req.getKpiValue()) && StringUtils.isNotBlank(req.getSort())) {
			sql.append("ORDER BY\n");
			sql.append("    ").append(req.getKpiValue()).append(" ").append(req.getSort()).append('\n');
		} else {
			sql.append("ORDER BY\n");
			sql.append("    day DESC\n");
		}

		Long current = req.getCurrent();
		Long size = req.getSize();
		if (Objects.nonNull(current) && Objects.nonNull(size)) {
			Long offset = (current - 1) * size;
			sql.append("LIMIT\n");
			sql.append("    ").append(offset).append(", ").append(size).append('\n');
		}
		log.info("注册留存率分列表查询SQL: [\n{}]", sql.toString());
		long start = System.currentTimeMillis();
		List<RegRetentionVO> regRetentionVOList = dorisTemplate.query(sql.toString(), new Object[]{}, new BeanPropertyRowMapper<>(RegRetentionVO.class));
		log.info("注册留存率分列表页查询耗时: {}ms", System.currentTimeMillis() - start);
		return regRetentionVOList;
	}


	private StringBuilder getBaseSql(RegRetentionDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT\n" +
				"\t\t\tIF(a.`day` is null,b.`day`,a.`day`\t) `day`,\n" +
				"\t\t\tCOALESCE(retentionNums,0) retentionNums ,-- 次留\n" +
				"\t\t\t\t COALESCE(retentionNums3,0) retentionNums3, -- 3留\n" +
				"\t\t\t\t COALESCE(retentionNums4,0) retentionNums4, -- 4留\n" +
				"\t\t\t\t COALESCE(retentionNums5,0) retentionNums5,-- 5留\n" +
				"\t\t\t\t COALESCE(retentionNums6,0) retentionNums6, -- 6留\n" +
				"\t\t\t\t COALESCE(retentionNums7,0) retentionNums7, -- 7留\n" +
				"\t\t\t\t COALESCE(retentionNums8,0) retentionNums8,  -- 8留\n" +
				"\t\t\t\t COALESCE(retentionNums9,0) retentionNums9,  -- 9留\n" +
				"\t\t\t\t COALESCE(retentionNums10,0) retentionNums10, -- 10留\n" +
				"\t\t\t\t COALESCE(retentionNums11,0) retentionNums11, -- 11留\n" +
				"\t\t\t\tCOALESCE(retentionNums12,0) retentionNums12, -- 12留\n" +
				"\t\t\t\tCOALESCE(retentionNums13,0) retentionNums13,  -- 13留\n" +
				"\t\t\t\tCOALESCE(retentionNums14,0) retentionNums14,  -- 14留\n" +
				"\t\t\t\tCOALESCE(retentionNums30,0) retentionNums30, -- 30留\n" +
				"\t\t\t\tCOALESCE(retentionNums60,0) retentionNums60, -- 60留\n" +
				"\t\t\t\tCOALESCE(retentionNums90,0)  retentionNums90, -- 90留\n" +
				"\t\t\t\tCOALESCE(retentionNums120,0) retentionNums120, -- 120留\n" +
				"\t\t\t\tCOALESCE(retentionNums150,0)  retentionNums150, -- 150留\n" +
				"\t\t\t\t COALESCE(retentionNums180,0) retentionNums180, -- 180留\n" +
				"\t\t\t\t COALESCE(retentionNums360,0)  retentionNums360, -- 360留\n" +
				"\t\t\tCOALESCE(accountNums,0) accountNums  -- 新增用户数\n" +
				"\t\t\t\n" +
				"\t\tFROM\n" +
				"\t\t\t(\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\tb.reg_date AS `day`,\n" +
				" count(distinct  case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 1 DAY ) then b.username else null  end )  retentionNums ,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 2 DAY ) then b.username else null  end  )  retentionNums3,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 3 DAY ) then b.username else null  end )  retentionNums4,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 4 DAY ) then b.username else null  end )  retentionNums5,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 5 DAY ) then b.username else null  end )  retentionNums6,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 6 DAY ) then b.username else null  end )  retentionNums7,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 7 DAY ) then b.username else null  end )  retentionNums8,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 8 DAY ) then b.username else null  end )  retentionNums9,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 9 DAY ) then b.username else null  end )  retentionNums10,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 10 DAY ) then b.username else null  end )  retentionNums11,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 11 DAY ) then b.username else null  end )  retentionNums12,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 12 DAY ) then b.username else null  end )  retentionNums13,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 13 DAY ) then b.username else null  end )  retentionNums14,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 29 DAY ) then b.username else null  end )  retentionNums30,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 59 DAY ) then b.username else null  end )  retentionNums60,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 89 DAY ) then b.username else null  end )  retentionNums90,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 119 DAY ) then b.username else null  end )  retentionNums120,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 149 DAY ) then b.username else null  end )  retentionNums150,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 179 DAY ) then b.username else null  end )  retentionNums180,\n" +
				" count(distinct case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 259 DAY ) then b.username else null  end )  retentionNums360\n" +
				"\t\t\t\tFROM \n")
				 .append(" ( select   active_date,username,app_main, app_code from  ")
				.append(yunYingProperties.getDailyDataActiveTableData()).append(" a \n")
				.append(" where ")
				.append(selectCommonCondition(req, 2)).append("\n")
				.append(" ) a \n")
				.append("\t\t\tinner JOIN ")
				.append("( SELECT username, reg_date, app_main, app_code FROM \n")
				.append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a where \n")
				.append(selectCommonCondition(req, 1)).append("\n")
				.append(") b ON a.app_main = b.app_main \n" +
						"\t\t\t\tAND a.username = b.username \n" +
						"\t\t\tGROUP BY\n" +
						"\t\t\t\tb.reg_date \n")
				.append("\t\t) a \n" +
						"\t\t\tFULL JOIN (\n" +
						"\t\t\tSELECT\n" +
						"\t\t\t\tCOUNT( DISTINCT username ) AS accountNums,\n" +
						"\t\t\t\treg_date AS `day` \n" +
						"\t\t\tFROM \n")
				.append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a where ")
				.append(selectCommonCondition(req, 1)).append("\n")
				.append("GROUP BY\n" +
						"\t\t\t\ta.reg_date \n" +
						"\t\t\t) b USING ( `day` ) \n");

		return sql;
	}

	public String selectCommonCondition(RegRetentionDto req, Integer num) {
		String startTime = req.getStartTime();
		String endTime = req.getEndTime();
		String pgidArr = req.getPgidArr();
		String gameidArr = req.getGameidArr();
		String parentchlArr = req.getParentchlArr();
		String chlArr = req.getChlArr();
		String appchlArr = req.getAppchlArr();
		StringBuilder commonCondition = new StringBuilder();
		commonCondition.append(" a.dx_app_code = ")
				.append(yunYingProperties.getDxAPPCode())
				.append(StringUtils.SPACE);
		if (StringUtils.isNotEmpty(pgidArr)) {
			commonCondition.append("and a.app_main in ( ").append(pgidArr).append(" )")
					.append(StringUtils.SPACE);
		}
		if (StringUtils.isNotEmpty(gameidArr)) {
			commonCondition.append("and a.app_code in ( ").append(gameidArr).append(" )")
					.append(StringUtils.SPACE);
		}
		if (num == 1) {
			commonCondition.append("and a.reg_date >= ").append("\'" + startTime + "\'")
					.append(StringUtils.SPACE)
					.append("and a.reg_date <= ").append("\'" + endTime + "\'")
					.append(StringUtils.SPACE);

		} else if (num == 2) {
			//活跃表数据与活跃渠道无关，只查询注册对应的渠道及分包编码
			String format = null;
			try {
				SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd");
				Date date = formatter.parse(endTime);
				Calendar c = Calendar.getInstance();
				c.setTime(date);
				c.add(Calendar.DAY_OF_MONTH, 360);
				Date time = c.getTime();
				format = formatter.format(time);
			} catch (ParseException e) {
				e.printStackTrace();
			}
			if (StringUtils.isEmpty(format)) {
				format = endTime;
			}
			commonCondition.append("and a.active_date >= ").append("\'" + startTime + "\'")
					.append(StringUtils.SPACE)
					.append("and a.active_date <= ").append("\'" + format + "\'")
					.append(StringUtils.SPACE);
			return commonCondition.toString();
		}

		if (StringUtils.isNotEmpty(parentchlArr)) {

			String[] split = parentchlArr.split(",");
			if (split != null && split.length > 0) {
				commonCondition.append("and a.chl_main in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}

		}
		if (StringUtils.isNotEmpty(chlArr) ) {
			String[] split = chlArr.split(",");
			if (split != null && split.length > 0) {
				commonCondition.append("and a.chl_sub in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);

			}
		}
		if (StringUtils.isNotEmpty(appchlArr)) {
			String[] split = appchlArr.split(",");
			if (split != null && split.length > 0) {
				commonCondition.append("and a.chl_app in ( ").append("'" + StringUtils.join(split, "','") + "'").append(" )")
						.append(StringUtils.SPACE);
			}
		}
		return commonCondition.toString();
	}

	public Long count(RegRetentionDto req) {
		final StringBuilder countSql = new StringBuilder();
		countSql.append("SELECT\n");
		countSql.append("    COUNT(1)\n");
		countSql.append("FROM\n");
		countSql.append("    (\n");
		countSql.append(getBaseSql(req));
		countSql.append("    ) a\n");
		return dorisTemplate.queryForObject(countSql.toString(), Long.class);
	}

	public List<RegRetentionVO> collect(RegRetentionDto req) {
		String sql = buildCollectSql(req);
		log.info("注册留存率数据汇总SQL: [\n{}]", sql);
		long start = System.currentTimeMillis();
		List<RegRetentionVO> regRetentionVOList = dorisTemplate.query(sql, new Object[]{}, new BeanPropertyRowMapper<>(RegRetentionVO.class));
		log.info("注册留存率汇总SQL查询耗时: {}ms", System.currentTimeMillis() - start);
		return regRetentionVOList;
	}

	private String buildCollectSql(RegRetentionDto req) {
		StringBuilder sql = new StringBuilder();
		sql.append("\t\tSELECT  '汇总' day, \n" +
				"\t\t\tSUM(accountNums) as accountNums, -- 新增用户数 \n" +
				"\t\t\tSUM(retentionRate) as retentionRate, -- 次留\n" +
				"\t\t\tSUM(retentionRate3) as retentionRate3, --3留\n" +
				"\t\t\tSUM(retentionRate4) as retentionRate4, -- 4留\n" +
				"\t\t\tSUM(retentionRate5) as retentionRate5, -- 5留\n" +
				"\t\t\tSUM(retentionRate6) as retentionRate6, -- 6留\n" +
				"\t\t\tSUM(retentionRate7) as retentionRate7, -- 7留\n" +
				"\t\t\tSUM(retentionRate8) as retentionRate8, -- 8留\n" +
				"\t\t\tSUM(retentionRate9) as retentionRate9, -- 9留\n" +
				"\t\t\tSUM(retentionRate10) as retentionRate10, --10留\n" +
				"\t\t\tSUM(retentionRate11) as retentionRate11, -- 11留\n" +
				"\t\t\tSUM(retentionRate12) as retentionRate12, -- 12留\n" +
				"\t\t\tSUM(retentionRate13) as retentionRate13, -- 13留\n" +
				"\t\t\tSUM(retentionRate14) as retentionRate14, -- 14留\n" +
				"\t\t\tSUM(retentionRate30) as retentionRate30, -- 30留\n" +
				"\t\t\tSUM(retentionRate60) as retentionRate60, -- 60留\n" +
				"\t\t\tSUM(retentionRate90) as retentionRate90, -- 90留\n" +
				"\t\t\tSUM(retentionRate120) as retentionRate120, -- 120留\n" +
				"\t\t\tSUM(retentionRate150) as retentionRate150, -- 150留\n" +
				"\t\t\tSUM(retentionRate180) as retentionRate180, -- 180留\n" +
				"\t\t\tSUM(retentionRate360) as retentionRate360 -- 360留\n" +
				"\t\t\tFROM\n" +
				"\t\t\t(\n" +
				"\t\t\tSELECT  \n" +
				"\t\t\tSUM(accountNums) AS accountNums, -- 新增用户数 \n" +
				"0 AS retentionRate,-- 次留\n" +
				"0 AS retentionRate3,-- 3留\n" +
				"0 AS retentionRate4,-- 4留\n" +
				"0 AS retentionRate5,-- 5留\n" +
				"0 AS retentionRate6,-- 6留\n" +
				"0 AS retentionRate7,-- 7留\n" +
				"0 AS retentionRate8,-- 8留\n" +
				"0 AS retentionRate9,-- 9留\n" +
				"0 AS retentionRate10,-- 10留\n" +
				"0 AS retentionRate11,-- 11留\n" +
				"0 AS retentionRate12,-- 12留\n" +
				"0 AS retentionRate13,-- 13留\n" +
				"0 AS retentionRate14,-- 14留\n" +
				"0 AS retentionRate30,-- 30留\n" +
				"0 AS retentionRate60,-- 60留\n" +
				"0 AS retentionRate90,-- 90留\n" +
				"0 AS retentionRate120,-- 120留\n" +
				"0 AS retentionRate150,-- 150留\n" +
				"0 AS retentionRate180,-- 180留\n" +
				"0 AS retentionRate360-- 360留\n" +
				"\t\tFROM \t\t\t\n" +
				"\t\t\t\t(\n" +
				"\t\t\t\t\n" +
				"\t\t\t\tSELECT\n" +
				"\t\t\t\t\t\tCOUNT( DISTINCT username ) AS accountNums -- 新增用户数 \n" +
				"\t\t\t\tFROM \n")
				.append("          ").append(yunYingProperties.getDailyDataAccountRegTableData()).append("  a \n")
				.append("                where ")
				.append("              ").append(selectCommonCondition(req, 1)).append("\n")
				.append("\t\t\t\t) a \n" +
						"          UNION ALL\n" +
						"            SELECT \n" +
						"  0 AS accountNums, -- 新增用户数\n "+
						"round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums)   * 100.00, SUM(accountNums) ), 0 ), 2 ) AS retentionRate,-- 次留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums3)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate3,-- 3留\n"+
						"round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums4)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate4,-- 4留\n"+
						"round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums5)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate5,-- 5留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums6)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate6,-- 6留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums7)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate7,-- 7留\n"+
						"round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums8)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate8,-- 8留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums9)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate9,-- 9留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums10)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate10,-- 10留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums11)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate11,-- 11留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums12)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate12,-- 12留\n"+
						"round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums13)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate13,-- 13留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums14)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate14,-- 14留\n"+
						"round( IF ( SUM(accountNums) > 0, divide ( SUM(retentionNums30)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate30,-- 30留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums60)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate60,-- 60留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums90)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate90,-- 90留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums120)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate120,-- 120留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums150)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate150,-- 150留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums180)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate180,-- 180留\n"+
						"round( IF (  SUM(accountNums) > 0, divide ( SUM(retentionNums360)   * 100.00, SUM(accountNums)  ), 0 ), 2 ) AS retentionRate360-- 360留\n"+
						"\t\t\t\t\t\t\t\tFROM\n" +
						"\t\t\t\t\n" +
						"                (SELECT\n" +
						"                       \n" +
						"                        retentionNums , -- 次留\n" +
						"                       retentionNums3,  -- 3留\n" +
						"                        retentionNums4 , -- 4留\n" +
						"                        retentionNums5 , -- 5留\n" +
						"                        retentionNums6, -- 6留\n" +
						"                        retentionNums7, -- 7留\n" +
						"                        retentionNums8 , -- 8留\n" +
						"                        retentionNums9,  -- 9留\n" +
						"                        retentionNums10, -- 10留\n" +
						"                        retentionNums11, -- 11留\n" +
						"                        retentionNums12, -- 12留\n" +
						"                        retentionNums13, -- 13留\n" +
						"                        retentionNums14, -- 14留\n" +
						"                       retentionNums30, -- 30留\n" +
						"                        retentionNums60, -- 60留\n" +
						"                        retentionNums90, -- 90留\n" +
						"                        retentionNums120, -- 120留\n" +
						"                        retentionNums150, -- 150留\n" +
						"                        retentionNums180, -- 180留\n" +
						"                        retentionNums360, -- 360留\n" +
						"                        accountNums -- 新增用户数\n" +
						"\n" +
						"                FROM\n" +

						"                        (\n" +
						"\t\t\t\tSELECT\n" +
						"\t\t\t\t\tb.reg_date AS `day`,\n" +
						" count(distinct  case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 1 DAY ) then b.username else null  end )  retentionNums ,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 2 DAY ) then b.username else null  end  )  retentionNums3,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 3 DAY ) then b.username else null  end )  retentionNums4,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 4 DAY ) then b.username else null  end )  retentionNums5,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 5 DAY ) then b.username else null  end )  retentionNums6,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 6 DAY ) then b.username else null  end )  retentionNums7,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 7 DAY ) then b.username else null  end )  retentionNums8,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 8 DAY ) then b.username else null  end )  retentionNums9,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 9 DAY ) then b.username else null  end )  retentionNums10,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 10 DAY ) then b.username else null  end )  retentionNums11,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 11 DAY ) then b.username else null  end )  retentionNums12,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 12 DAY ) then b.username else null  end )  retentionNums13,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 13 DAY ) then b.username else null  end )  retentionNums14,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 29 DAY ) then b.username else null  end )  retentionNums30,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 59 DAY ) then b.username else null  end )  retentionNums60,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 89 DAY ) then b.username else null  end )  retentionNums90,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 119 DAY ) then b.username else null  end )  retentionNums120,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 149 DAY ) then b.username else null  end )  retentionNums150,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 179 DAY ) then b.username else null  end )  retentionNums180,\n" +
						" count(case when  a.active_date = DATE_SUB(b.reg_date, INTERVAL  - 259 DAY ) then b.username else null  end )  retentionNums360\n" +
						"                        FROM\n" +
						"\t\t\t\t( SELECT  active_date,username,app_main, app_code " +
						"  FROM ")
				.append(yunYingProperties.getDailyDataActiveTableData()).append("  a  \n")
				.append("                      ").append(" where \n")
				.append("                      ").append(selectCommonCondition(req, 2)).append("\n")
				.append("                       ) a \n")
				.append("                         inner JOIN ")
				.append("                        ( SELECT username, reg_date, app_main, app_code FROM \n")
				.append("                           ").append(yunYingProperties.getDailyDataAccountRegTableData()).append(" a  where \n")
				.append("                             ").append(selectCommonCondition(req, 1)).append("\n")
				.append("                           ) b ON a.app_main = b.app_main \n" +
						"                                AND a.username = b.username \n" +

						"                             GROUP BY\n" +
						"                                b.reg_date \n" +
						"                             ) a\n" +
						"                    FULL JOIN (\n" +
						"                        SELECT\n" +
						"                            COUNT( DISTINCT username ) AS accountNums,\n" +
						"                            reg_date AS `day` \n" +
						"                        FROM \n")
				.append("                          ").append(yunYingProperties.getDailyDataAccountRegTableData()).append("  a where \n")
				.append("                              ").append(selectCommonCondition(req, 1))
				.append("                      GROUP BY\n" +
						"                                a.reg_date \n" +
						"                                     ) b USING ( `day` ) )  a  ) a \n");
		return sql.toString();
	}
}
